﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using EffiProzDB.Persist;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TestGroupByHaving
    /// </summary>
    [TestClass]
    public class TestGroupByHaving : BaseTest
    {
        public TestGroupByHaving()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void GroupByHaving()
        {
            CleanUp("GroupByHaving");
            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "GroupByHaving",
                   user, password, props);

            setUp();

            testAggregatedGroupBy();
            testAggregatedGroupByHaving1();
            testAggregatedGroupByHaving2();
            testHavingWithoutGroupBy1();
            testInvalidHaving();
            testHavingWithoutGroupBy2();
        }

        protected void setUp()
        {

            string sql = "create table employee(id int, "
                         + "firstname VARCHAR(50), " + "lastname VARCHAR(50), "
                         + "salary decimal(10, 2), " + "superior_id int, "
                         + "CONSTRAINT PK_employee PRIMARY KEY (id), "
                         + "CONSTRAINT FK_superior FOREIGN KEY (superior_id) "
                         + "REFERENCES employee(ID))";

            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

            addEmployee(1, "Mike", "Smith", 160000, -1);
            addEmployee(2, "Mary", "Smith", 140000, -1);

            // Employee under Mike
            addEmployee(10, "Joe", "Divis", 50000, 1);
            addEmployee(11, "Peter", "Mason", 45000, 1);
            addEmployee(12, "Steve", "Johnson", 40000, 1);
            addEmployee(13, "Jim", "Hood", 35000, 1);

            // Employee under Mike
            addEmployee(20, "Jennifer", "Divis", 60000, 2);
            addEmployee(21, "Helen", "Mason", 50000, 2);
            addEmployee(22, "Daisy", "Johnson", 40000, 2);
            addEmployee(23, "Barbara", "Hood", 30000, 2);
        }

        private void addEmployee(int id, String firstName, String lastName,
                            double salary, int superiorId)
        {

            string sql = "insert into employee values(" + id + ", '" + firstName
                        + "', '" + lastName + "', " + salary + ", "
                        + (superiorId <= 0 ? "null"
                                           : ("" + superiorId)) + ")";
            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, sql);

        }

        /**
        * Tests aggregated selection with a <b>GROUP_BY</b> clause.  This is
        * a normal use of the <b>GROUP_BY</b> clause.  The first two employees
        * do not have a superior, and must be grouped within the same group,
        * according to <b>GROUP_BY</b> standard.
        */
        public void testAggregatedGroupBy()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "group by superior_id " + "order by superior_id " + "";
            Object[][] expected = new Object[][] {
            new object[]{
                (decimal)(150000), (2)
            },
             new object[]{
                (decimal)(42500), (13)
            }, 
             new object[]{
                (decimal)(45000), (23)
            },
        };

            compareResults(sql, expected, 0);
        }

        /**
        * Tests an unusual usage of the <b>HAVING</b> clause, without a
        * <b>GROUP BY</b> clause.
        * <p>
        * Only one row is returned by the aggregate selection without a
        * <b>GROUP BY</b> clause.  The <b>HAVING</b> clause is applied to the
        * only returned row.  In this case, the <b>HAVING</b> condition is
        * satisfied.
        */
        public void testHavingWithoutGroupBy1()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "having avg(salary) > 1000 " + "";
            Object[][] expected = new Object[][] {
                new object[]{
                    (decimal)65000, 23
                },
            };

            compareResults(sql, expected, 0);
        }

        /**
        * Tests an unusual usage of the <b>HAVING</b> clause, without a
        * <b>GROUP BY</b> clause.
        * <p>
        * Only one row is returned by the aggregate selection without a
        * <b>GROUP BY</b> clause.  The <b>HAVING</b> clause is applied to the
        * only returned row.  In this case, the <b>HAVING</b> condition is
        * NOT satisfied.
        */
        public void testHavingWithoutGroupBy2()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "having avg(salary) > 1000000 " + "";
            Object[][] expected = new Object[][] { };

            compareResults(sql, expected, 0);
        }

        /**
        * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
        * <b>HAVING</b> clause.
        * <p>
        * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
        * The first two employees are eliminated due to the <b>HAVING</b>
        * condition.
        * <p>
        * This test uses <b>GROUP_BY</b> column to eliminate first group.
        */
        public void testAggregatedGroupByHaving2()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "group by superior_id "
                         + "having superior_id is not null "
                         + "order by superior_id " + "";
            Object[][] expected = new Object[][] {
            new object[]{
                (decimal)(42500), (int)(13)
            }, 
            new object[]{
                (decimal)(45000), (int)(23)
            },
        };

            compareResults(sql, expected, 0);
        }


        /**
        * Tests an invalid <b>HAVING</b> clause that contains columns not in
        * the <b>GROUP BY</b> clause.  A SQLException should be thrown.
        */
        public void testInvalidHaving()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "group by lastname "
                         + "having (max(id) > 1) and (superior_id > 1) " + "";
            Object[][] expected = new Object[][] { };

            compareResults(sql, expected, -Trace.NOT_IN_AGGREGATE_OR_GROUP_BY);
        }

        /**
        * Tests aggregated selection with a <b>GROUP_BY</b> clause and a
        * <b>HAVING</b> clause.
        * <p>
        * This is a typical use of the <b>GROUP_BY</b> + <b>HAVING</b> clause.
        * The first two employees are eliminated due to the <b>HAVING</b>
        * condition.
        * <p>
        * This test uses aggregated function to eliminate first group.
        */
        public void testAggregatedGroupByHaving1()
        {

            String sql = "select avg(salary), max(id) from employee "
                         + "group by superior_id " + "having max(id) > 5 "
                         + "order by superior_id " + "";
            Object[][] expected = new Object[][] {
            new object[]{
                (decimal)(42500), (int)(13)
            }, 
            new object[]{
                (decimal)(45000), (int)(23)
            },
        };

            compareResults(sql, expected, 0);
        }



        //------------------------------------------------------------
        // Helper methods
        //------------------------------------------------------------
        private void compareResults(String sql, Object[][] rows,
                                    int errorCode)
        {

            Result rs = null;

            if (errorCode == 0)
                rs = TestHelper.ExecuteSelectSQL(sessionProxy, sql, rows.GetLength(0));
            else
            {
                TestHelper.ExecuteSelectSQL_Failure(sessionProxy, sql);
                return;
            }

            //Assert.IsTrue((0 == errorCode), "Statement <" + sql + "> \nexpecting error code: "
            //           + errorCode);



            int colCount = rows.Length > 0 ? rows[0].Length
                                           : 0;


            for (int rowCount = 0; rowCount < rs.getSize(); rowCount++)
            {
                Assert.IsTrue((rowCount < rows.Length), "Statement <" + sql + "> \nreturned too many rows.");

                TestHelper.MatchSingleResultRow(rs, rowCount, rows[rowCount]);

            }

        }

    }
}
